﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Sinacor.Infra.Service.Data;
using System.Data.Common;
using Sinacor.OMM;
using Sinacor.OMM.ConnectionManager.DataBase;
using System.Diagnostics;
using Sinacor.OMM.Util;

namespace Sinacor.OMM.Router.ResourceAccess
{
    public class Order : IDisposable
    {
        private DataBaseCommunicator _dataBaseCommunicator;
        private TraceWriter _tracerWriter;

        public Order(DataBaseCommunicator dataBaseCommunicator)
        {
            _dataBaseCommunicator = dataBaseCommunicator;
            _tracerWriter = Tracer.GetInstance("Roteador", "ResourceAccess.log");
        }

        public void InsertCblcOrders(List<MessageEntity.STM.BaseMessage> orders)
        {
            StringBuilder query = new StringBuilder();
            MessageEntity.STM.MessageAN53 messageAN53;
            //Stopwatch watch = new Stopwatch();
            DateTime date;

            query.Append("BEGIN ");

            foreach (var item in orders)
            {
                query.AppendFormat("PR_NEGOCIOS.pOr_OMM_Inserir_TORHUB_TXT (TO_DATE('{0}', 'dd/mm/yyyy'), '{1}', '{2}', '{3}');",
                    item.OperationDate.ToString("dd/MM/yyyy"), item.System, item.Original, item.ID);

                if (item is MessageEntity.STM.MessageAN53)
                {
                    date = DateTime.Now;
                    messageAN53 = (MessageEntity.STM.MessageAN53)item;
                    query.AppendFormat(" PR_TITULO.pOr_OMM_Inserir_TORHUBPAP(v_dt_sistema => TO_DATE('{0}', 'dd/mm/yyyy hh24:mi:ss'), ",
                        new DateTime(item.OperationDate.Year, item.OperationDate.Month, item.OperationDate.Day, date.Hour, date.Minute, date.Second).ToString("dd/MM/yyyy HH:mm:ss"));

                    query.AppendFormat("v_cd_codisi => '{0}', ", messageAN53.COD_ISIN);
                    query.AppendFormat("v_nr_dismes => {0}, ", messageAN53.NUM_DIST);
                    query.AppendFormat("v_cd_negocio => '{0}', ", messageAN53.COD_NEG);
                    query.AppendFormat("v_nm_nompre => '{0}', ", messageAN53.NOME_PREG);
                    query.AppendFormat("v_nm_especi => {0}, ", (string.IsNullOrEmpty(messageAN53.COD_ESP)) ? null : string.Concat("'", messageAN53.COD_ESP, "'"));
                    query.AppendFormat("v_cd_nrmerc => {0}, ", messageAN53.COD_TIPO_MERC);
                    query.AppendFormat("v_nr_nserie => {0}, ", messageAN53.NUM_SERI);

                    query.AppendFormat("v_dt_inineg => {0}, ", (!messageAN53.DATA_INIC_NEG_ATIV_MERC.HasValue) ? null :
                        string.Format("'{0}'", messageAN53.DATA_INIC_NEG_ATIV_MERC.Value.ToString("yyyy-MM-dd")));

                    query.AppendFormat("v_dt_limneg => {0}, ", (!messageAN53.DATA_LIM_NEG_ATIV_MERC.HasValue) ? null :
                        string.Format("'{0}'", messageAN53.DATA_LIM_NEG_ATIV_MERC.Value.ToString("yyyy-MM-dd")));

                    query.AppendFormat("v_vl_preexe => TO_NUMBER(REPLACE('{0}', ',', '.')), ", messageAN53.VAL_PREC_EXER);

                    query.AppendFormat("v_dt_datven => {0}, ", (!messageAN53.DATA_VENC.HasValue) ? null :
                        string.Format("'{0}'", messageAN53.DATA_VENC.Value.ToString("yyyy-MM-dd")));

                    query.AppendFormat("v_nr_fatcot => {0}, ", messageAN53.FAT_COT_PREC);
                    query.AppendFormat("v_cd_titobj => '{0}', ", messageAN53.COD_NEG_PAP_OBJT);
                    query.AppendFormat("v_in_indmer => {0}, ", messageAN53.COD_SIST_NEG);
                    query.AppendFormat("v_nr_lotneg => {0}); ", messageAN53.QTE_MINI_LOTE_PAD);
                }
            }
            query.Append(" COMMIT;");
            query.Append(" END;");

            //watch.Start();            
            _dataBaseCommunicator.ExecuteNonQuery(query.ToString());
            //watch.Stop();
            //_tracerWriter.Trace("tempo de insert torhub: " + watch.ElapsedMilliseconds + " count: " + orders.Count);
        }

        public void InsertMegaBolsaOrders(List<MessageEntity.STM.BaseMessage> orders)
        {
            StringBuilder query = new StringBuilder();
            //Stopwatch watch = new Stopwatch();

            query.Append("DECLARE v_dt_sistema TORSINL_OFER.DT_SISTEMA%TYPE; ");
            query.Append("BEGIN ");
            query.Append("v_dt_sistema := PGE_GERAL.FGE_GETTMST(); ");

            foreach (var item in orders)
            {
                query.AppendFormat("PR_OFMEGA.pOr_OMM_Inserir_TORSINL_OFER (TO_DATE('{0}', 'dd/mm/yyyy'), v_dt_sistema, '{1}', 0, '{2}');",
                    item.OperationDate.ToString("dd/MM/yyyy"), item.Original, item.ID);
            }
            query.Append(" COMMIT;");
            query.Append(" END;");

            //watch.Start();
            _dataBaseCommunicator.ExecuteNonQuery(query.ToString());
            //watch.Stop();
            //_tracerWriter.Trace("tempo de insert: " + watch.ElapsedMilliseconds + " count: " + orders.Count);
        }

        public int GetDatabaseQueueCount(DateTime operationDate)
        {
            StringBuilder query = new StringBuilder();

            query.Append("SELECT SUM(total) total FROM ");
            query.Append("(");
            query.Append(" SELECT COUNT(*) total FROM TORSINL_OFER");
            query.Append(" WHERE SUBSTR(COD_MSG_HUB_BVMF, 0, 8) = '" + operationDate.ToString("yyyyMMdd") + "'");
            query.Append(" UNION");
            query.Append(" SELECT COUNT(*) FROM TORHUB_TXT");
            query.Append(" WHERE SUBSTR(COD_MSG_HUB_BVMF, 0, 8) = '" + operationDate.ToString("yyyyMMdd") + "'");
            query.Append(")");

            return Convert.ToInt32(_dataBaseCommunicator.ExecuteScalar(query.ToString()));
        }

        public List<string> GetLastProcessedMessageIds(DateTime operationDate)
        {
            StringBuilder query = new StringBuilder();
            List<string> messageIds = new List<string>();
            System.Data.IDataReader reader;

            query.Append("SELECT COD_ULT_MSG_PROS FROM ");
            query.Append("(");
            query.AppendFormat(" SELECT NVL(COD_ULT_MSG_PROS, '{0}A0000000000') COD_ULT_MSG_PROS FROM TBOPROS_IAFR ", operationDate.ToString("yyyyMMdd"));
            query.Append(") ");
            query.Append("WHERE SUBSTR(COD_ULT_MSG_PROS, 0, 8) = '" + operationDate.ToString("yyyyMMdd") + "'");

            query.Append("UNION ");

            query.Append("SELECT COD_ULT_MSG_PROS FROM ");
            query.Append("(");
            query.AppendFormat(" SELECT NVL(COD_ULT_MSG_PROS, '{0}A0000000000') COD_ULT_MSG_PROS FROM TORSLESEQ ", operationDate.ToString("yyyyMMdd"));
            query.Append(") ");
            query.Append("WHERE SUBSTR(COD_ULT_MSG_PROS, 0, 8) = '" + operationDate.ToString("yyyyMMdd") + "'");

            query.Append("UNION ");

            query.Append("SELECT COD_ULT_MSG_PROS FROM ");
            query.Append("(");
            query.AppendFormat(" SELECT NVL(COD_ULT_MSG_PROS, '{0}A0000000000') COD_ULT_MSG_PROS FROM TORNEGSEQ ", operationDate.ToString("yyyyMMdd"));
            query.Append(") ");
            query.Append("WHERE SUBSTR(COD_ULT_MSG_PROS, 0, 8) = '" + operationDate.ToString("yyyyMMdd") + "'");

            reader = _dataBaseCommunicator.ExecuteReader(query.ToString());

            while (reader.Read())
                messageIds.Add(reader.GetValue<string>("COD_ULT_MSG_PROS"));

            reader.Close();
            reader.Dispose();
            reader = null;
            return messageIds;
        }

        public string GetLastMegaBolsaMessageId(DateTime operationDate)
        {
            StringBuilder query = new StringBuilder();
            string messageId = string.Empty;
            System.Data.IDataReader reader;

            query.Append("SELECT COD_ULT_MSG_PROS FROM TORSLESEQ ");
            query.Append("WHERE SUBSTR(COD_ULT_MSG_PROS, 0, 8) = '" + operationDate.ToString("yyyyMMdd") + "'");

            reader = _dataBaseCommunicator.ExecuteReader(query.ToString());

            if (reader.Read())
                messageId = reader.GetValue<string>("COD_ULT_MSG_PROS");

            reader.Close();
            reader.Dispose();
            reader = null;
            return messageId;
        }

        public string GetLastCblcMessageId(DateTime operationDate)
        {
            StringBuilder query = new StringBuilder();
            string messageId = string.Empty;
            System.Data.IDataReader reader;

            query.Append("SELECT COD_ULT_MSG_PROS FROM TORNEGSEQ ");
            query.Append("WHERE SUBSTR(COD_ULT_MSG_PROS, 0, 8) = '" + operationDate.ToString("yyyyMMdd") + "'");

            reader = _dataBaseCommunicator.ExecuteReader(query.ToString());

            if (reader.Read())
                messageId = reader.GetValue<string>("COD_ULT_MSG_PROS");

            reader.Close();
            reader.Dispose();
            reader = null;
            return messageId;
        }

        public void ResetSequence()
        {
            StringBuilder query = new StringBuilder();
            query.Append("begin ");
            query.Append(" PGE_GERAL.pGe_ReinicializaSeq('storhub_txt'); ");
            query.Append(" PGE_GERAL.pGe_ReinicializaSeq('storsinl_ofer'); ");
            query.Append("end; ");
            _dataBaseCommunicator.ExecuteNonQuery(query.ToString());
        }


        /// <summary>
        /// 
        /// </summary>
        public void ResetSequentialNumber()
        {
            StringBuilder query = new StringBuilder();

            query.Append("begin ");
            query.Append("update tornegseq set nr_seq = 0; ");
            query.Append("update torsleseq set nr_seq = 0; ");
            query.Append("commit; ");
            query.Append("end; ");

            _dataBaseCommunicator.ExecuteNonQuery(query.ToString());
        }

        /// <summary>
        /// Gets the count of routed messages for the given operation date.
        /// </summary>
        /// <param name="operationDate"></param>
        /// <returns></returns>
        public int GetRoutedMessagesCount(DateTime operationDate)
        {
            StringBuilder query = new StringBuilder();
            int count = 0;
            System.Data.IDataReader reader;

            query.Append("SELECT COUNT(*) TOTAL FROM ");
            query.Append("( ");
            query.Append("SELECT cod_ult_msg_pros FROM tbopros_iafr WHERE SUBSTR(COD_ULT_MSG_PROS, 0, 8) = '" + operationDate.ToString("yyyyMMdd") + "'");
            query.Append("UNION ALL ");
            query.Append("SELECT cod_ult_msg_pros FROM TORSLESEQ WHERE SUBSTR(COD_ULT_MSG_PROS, 0, 8) = '" + operationDate.ToString("yyyyMMdd") + "'");
            query.Append("UNION ALL ");
            query.Append("SELECT cod_ult_msg_pros FROM TORNEGSEQ WHERE SUBSTR(COD_ULT_MSG_PROS, 0, 8) = '" + operationDate.ToString("yyyyMMdd") + "'");
            query.Append("UNION ALL ");
            query.Append("SELECT cod_msg_hub_bvmf FROM torhub_txt WHERE SUBSTR(cod_msg_hub_bvmf, 0, 8) = '" + operationDate.ToString("yyyyMMdd") + "'");
            query.Append("UNION ALL ");
            query.Append("SELECT cod_msg_hub_bvmf FROM torsinl_ofer WHERE SUBSTR(cod_msg_hub_bvmf, 0, 8) =  '" + operationDate.ToString("yyyyMMdd") + "'");
            query.Append(") ");
            reader = _dataBaseCommunicator.ExecuteReader(query.ToString());

            if (reader.Read())
                count = reader.GetValue<int>("TOTAL");

            reader.Close();
            reader.Dispose();
            reader = null;

            return count;
        }

        #region IDisposable Members

        public void Dispose()
        {
            _dataBaseCommunicator.Dispose();
            _dataBaseCommunicator = null;
        }

        #endregion
    }
}
